#coding:utf8
import MySQLdb as  connector

sql_create_table = """CREATE TABLE `levelup_module` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `LoginChannel` varchar(80) NOT NULL,
  `Vuid` varchar(80) NOT NULL,
  `Levels` int(11) DEFAULT '0',
  `VipLevel` int(11) DEFAULT '0',
  `BeforeLevel` int(11) DEFAULT '0',
  `AfterLevel` int(11) DEFAULT '0',
  `ILevelType` int(11) DEFAULT '0',
  `Times` int(11) DEFAULT '0',
  `Reason` varchar(80) NOT NULL,
  `ExtendField` varchar(40) NOT NULL,
  `opTime` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
"""
#drop TRIGGER if EXISTS `before_insert_levelup_module`;//delimiter ;delimiter //
sql2="""
CREATE  TRIGGER `before_insert_levelup_module` BEFORE INSERT ON `levelup_module`
FOR EACH ROW
BEGIN
	DECLARE last_time int;
	select opTime into last_time from levelup_module where Vuid = new.Vuid and Levels=new.BeforeLevel;
	if ISNULL(last_time) then SET NEW.Times = 0; ELSE SET NEW.Times = (new.opTime - last_time); end if;
END;

"""
sql3="drop TRIGGER if EXISTS `before_insert_levelup_module`"



def main():
    config = {'host': '127.0.0.1',  # 默认127.0.0.1
              'user': 'root',
              'passwd': 'root',
              'port': 3306,  # 默认即为3306
              'db': 'test',
              'charset': 'utf8'  # 默认即为utf8
              }
    try:
        cnn = connector.connect(**config)
    except connector.Error as e:
        print('connect fails!{}'.format(e))

    cursor = cnn.cursor()
    try:
        cursor.execute(sql2)
    except connector.Error as e:
        print('create table orange fails!{}'.format(e))


if __name__ =="__main__":
    main()


